﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Xml.Serialization;

namespace Kwik.Orm
{
	/// <summary>
	/// Maps database connections to ISchemaTable objects
	/// </summary>
	[XmlRoot("Schema")]
	public class BaseSchema : ISchema, IXmlSerializable
	{
		/// <summary>
		/// Create a new mapper object
		/// </summary>
		public BaseSchema(){}

		/// <summary>
		/// Create a mapper object with supplied connectionstring and providername
		/// </summary>
		/// <param name="connectionString">Connection string</param>
		/// <param name="providerName">Provider name</param>
		public BaseSchema(string connectionString, string providerName)
		{
			this.ConnectionString = connectionString;
			this.ProviderName = providerName;
		}

		private readonly IDictionary<string, ISchemaTable> _Tables = new Dictionary<string, ISchemaTable>();
		/// <summary>
		/// Collection of tables by name
		/// </summary>
		public IDictionary<string, ISchemaTable> Tables { get { return _Tables; } }

		/// <summary>
		/// Provider type name
		/// </summary>
		public string ProviderName { get; set; }
		/// <summary>
		/// Connection parameters string
		/// </summary>
		public string ConnectionString { get; set; }


		/// <summary>
		/// Load the schema using the specified connectionstring and providername
		/// </summary>
		/// <param name="connectionString">Name value collection of database parameters</param>
		/// <param name="providerName">Name of the provider to use</param>
		public virtual void Load(string connectionString, string providerName)
		{
			this.ConnectionString = connectionString;
			this.ProviderName = providerName;
			this.Load();
		}

		/// <summary>
		/// Load the schema with defined connectionstring and providername
		/// </summary>
		public virtual void Load()
		{
			DbProviderFactory factory = DbProviderFactories.GetFactory(this.ProviderName);
			DbConnection conn = factory.CreateConnection();
			conn.ConnectionString = this.ConnectionString;
			conn.Open();
			this.Load(conn);
			conn.Close();
			conn.Dispose();
		}

		/// <summary>
		/// Load the tables for the supplied connection using GetSchema("Table") on the supplied connection
		/// The tables are populated with columns read from a KeyInfo reader
		/// Constraints are database specific and should be added in a provider specific subclass
		/// </summary>
		/// <param name="conn">DbConnection to use for loading the schema</param>
		protected void Load(DbConnection conn)
		{
			this.Tables.Clear();

			if(conn.State == ConnectionState.Closed)conn.Open();

			//set the restrictions for table retreival
			string[] restrictions = new string[4];
			restrictions[3] = "TABLE";

			DataTable tables = conn.GetSchema("Tables",  restrictions);
			if (tables.Rows.Count == 0)
			{
				Trace.WriteLine("Kwik.Orm.Mapper.Load: Loading tables using 'BASE TABLE' restriction");
				restrictions[3] = "BASE TABLE";
				tables = conn.GetSchema("Tables", restrictions);
			}

			Trace.WriteLine("Kwik.Orm.Mapper.Load: " + tables.Rows.Count.ToString() + " tables loaded");

			foreach (DataRow rowTable in tables.Rows)
			{
				string tableName = (string)rowTable["TABLE_NAME"];
				if (this.Tables.ContainsKey(tableName)) continue;

				ISchemaTable table = new Table(tableName);
				this.Tables.Add(table.Name, table);
				Trace.WriteLine("Kwik.Orm.Mapper.Load: Table '" + table.Name + "' added");

				this.LoadColumns(table, conn);
			}
		}

		/// <summary>
		/// Load the columns
		/// </summary>
		/// <param name="table"></param>
		/// <param name="conn"></param>
		private void LoadColumns(ISchemaTable table, DbConnection conn)
		{

				//command to get the keyinfo for the table's columns 
				DbCommand cmd = conn.CreateCommand();
				cmd.CommandText = "select * from [" + table.Name.Replace("'", "''") + "] where 0=1";
				DbDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
				DataTable keyInfo = reader.GetSchemaTable();
				reader.Close();

				foreach (DataRow rowColumn in keyInfo.Rows)
				{
					ISchemaColumn column = new Column((string)rowColumn["ColumnName"]);
					if (table.Columns.ContainsKey(column.Name)) continue;
					column.Ordinal = Convert.ToInt32(rowColumn["ColumnOrdinal"]);
					column.Size = Convert.ToInt32(rowColumn["ColumnSize"]);
					column.Precision = Convert.ToInt32(rowColumn["NumericPrecision"]);
					column.Scale = Convert.ToInt32(rowColumn["NumericScale"]);
					column.IsAutoIncrement = Convert.ToBoolean(rowColumn["IsAutoIncrement"]);
					column.IsKey = Convert.ToBoolean(rowColumn["IsKey"]);
					column.IsNullable = Convert.ToBoolean(rowColumn["AllowDBNull"]);
					column.IsUnique = Convert.ToBoolean(rowColumn["IsUnique"]);
					column.Type = Convert.ToString(rowColumn["DataType"]);
					
					table.Columns.Add(column.Name, column);

					Trace.WriteLine("Kwik.Orm.Mapper.Load: Column '" + column.Name + "' added");

					this.LoadConstraints(column, table);
				}
		}

		/// <summary>
		/// Load the constraints from column specs
		/// </summary>
		/// <param name="column"></param>
		/// <param name="table"></param>
		private void LoadConstraints(ISchemaColumn column, ISchemaTable table)
		{

			if (column.IsKey)
			{
				Constraint constraint = new Constraint("PK_" + table.Name);
				constraint.Type = ConstraintType.PrimaryKey;
				if (!table.Constraints.ContainsKey(constraint.Name))
				{
					table.Constraints.Add(constraint.Name, constraint);
					Trace.WriteLine("Kwik.Orm.Mapper.Load: Constraint '" + constraint.Name + "' added");
				}
			}
			if (!column.IsNullable && !column.IsAutoIncrement)
			{
				Constraint constraint = new Constraint("NN_" + column.Name);
				constraint.Type = ConstraintType.NotNull;
				if (!table.Constraints.ContainsKey(constraint.Name))
				{
					table.Constraints.Add(constraint.Name, constraint);
					Trace.WriteLine("Kwik.Orm.Mapper.Load: Constraint '" + constraint.Name + "' added");
				}
			}
			if (column.IsUnique && !column.IsKey)
			{
				Constraint constraint = new Constraint("UC_" + column.Name);
				constraint.Type = ConstraintType.Unique;
				if (!table.Constraints.ContainsKey(constraint.Name))
				{
					table.Constraints.Add(constraint.Name, constraint);
					Trace.WriteLine("Kwik.Orm.Mapper.Load: Constraint '" + constraint.Name + "' added");
				}
			}
		}
		
		#region IXmlSerializable Members

		public System.Xml.Schema.XmlSchema GetSchema()
		{
			throw new NotImplementedException();
		}

		public void ReadXml(System.Xml.XmlReader reader)
		{
			throw new NotImplementedException();
		}

		public void WriteXml(System.Xml.XmlWriter writer)
		{
			XmlSerializer serializer = new XmlSerializer(typeof(Table));

			writer.WriteAttributeString("ConnectionString", this.ConnectionString);
			writer.WriteAttributeString("ProviderName", this.ProviderName);

			writer.WriteStartElement("Tables");
			foreach (Table table in this.Tables.Values) serializer.Serialize(writer, table);

			writer.WriteEndElement();
		}

		#endregion
		
	}
}
